Mapa Accidentes

1. Conexión de la BBDD a R

#install.packages("RPostgreSQL")
require("RPostgreSQL")

## Loading required package: RPostgreSQL

## Loading required package: DBI

# Guardar el Password para poder posteriormente eliminarlo
pw <- {"ludo"}

# Leer el driver de PostgreSQL
drv <- dbDriver("PostgreSQL")

# Crear la conexion con la base de datos
con <- dbConnect(drv, dbname = "accidentes_barcelona",
                 host = "192.168.0.165", port = 5432,
                 user = "postgres", password = pw)

# Eliminar el Password
rm(pw) 

# Chequear que no existe la tabla de la base de datos
dbExistsTable(con, "descripcion_marca_vehiculo")

## [1] TRUE

2. Extracción de los datos necesarios por el estudio

Se busca el código del barrio la Dreta de l’Eixample.

barrios <- dbGetQuery(con, "SELECT * FROM descripcion_barrio_accidente")
barrios

##    id_desc_barrio                                  desc_barrio
## 1              64                   el Camp de l'Arpa del Clot
## 2              65                                      el Clot
## 3              72                     Sant Martí de Provençals
## 4              73                          la Verneda i la Pau
## 5              70                        el Besòs i el Maresme
## 6              71                      Provençals del Poblenou
## 7              68                                  el Poblenou
## 8              69 Diagonal Mar i el Front Marítim del Poblenou
## 9              66            el Parc i la Llacuna del Poblenou
## 10             67                la Vila Olímpica del Poblenou
## 11              1                                     el Raval
## 12              2                               el Barri Gòtic
## 13              3                               la Barceloneta
## 14              4        Sant Pere, Santa Caterina i la Ribera
## 15             10                                  Sant Antoni
## 16              9               la Nova Esquerra de l'Eixample
## 17              8              l'Antiga Esquerra de l'Eixample
## 18              7                       la Dreta de l'Eixample
## 19              6                           la Sagrada Família
## 20              5                                el Fort Pienc
## 21             11                                 el Poble Sec
## 22             12                   la Marina del Prat Vermell
## 23             13                            la Marina de Port
## 24             14                        la Font de la Guatlla
## 25             15                                  Hostafrancs
## 26             16                                   la Bordeta
## 27             17                                Sants - Badal
## 28             18                                        Sants
## 29             19                                    les Corts
## 30             20                   la Maternitat i Sant Ramon
## 31             21                                    Pedralbes
## 32             26                       Sant Gervasi - Galvany
## 33             23                                       Sarrià
## 34             24                              les Tres Torres
## 35             25                   Sant Gervasi - la Bonanova
## 36             27                         el Putxet i el Farró
## 37             22        Vallvidrera, el Tibidabo i les Planes
## 38             28                    Vallcarca i els Penitents
## 39             30                                     la Salut
## 40             29                                      el Coll
## 41             31                            la Vila de Gràcia
## 42             32           el Camp d'en Grassot i Gràcia Nova
## 43             38                                 la Teixonera
## 44             39                     Sant Genís dels Agudells
## 45             41                             la Vall d'Hebron
## 46             40                                      Montbau
## 47             36                         la Font d'en Fargues
## 48             42                                     la Clota
## 49             43                                        Horta
## 50             34                                     Can Baró
## 51             37                                    el Carmel
## 52             33                             el Baix Guinardó
## 53             35                                  el Guinardó
## 54             54                                   Torre Baró
## 55             49                                    Canyelles
## 56             50                                 les Roquetes
## 57             48                                 la Guineueta
## 58             53                             la Trinitat Nova
## 59             51                                       Verdun
## 60             52                               la Prosperitat
## 61             44                Vilapicina i la Torre Llobeta
## 62             45                                        Porta
## 63             46                          el Turó de la Peira
## 64             61                                   la Sagrera
## 65             62                     el Congrés i els Indians
## 66             63                                        Navas
## 67             60                                  Sant Andreu
## 68             57                            la Trinitat Vella
## 69             58                                Baró de Viver
## 70             59                                el Bon Pastor
## 71             -1                                   Desconegut
## 72             56                                     Vallbona
## 73             47                                  Can Peguera
## 74             55                             Ciutat Meridiana

Este barrio tiene código 7. Entonces se buscan latitud y longitud de las ubicaciones de los accidentes en este barrio.

datos <- dbGetQuery(con, "SELECT latitud, longitud FROM accidente WHERE id_desc_barrio = 7 AND latitud != -1.0 AND longitud != -1.0")
head(datos)

##    latitud longitud
## 1 41.39170 2.174108
## 2 41.39463 2.165751
## 3 41.39910 2.166929
## 4 41.39988 2.173511
## 5 41.39790 2.172665
## 6 41.39372 2.160864

3. Diseño del mapa de accidentes

Se instalan unas librerias necesarias para diseñar los puntos en un mapa.

#install.packages("devtools")
library(devtools)
#install_github("rOpenSpain/caRtociudad")
library(caRtociudad)
#install.packages("ggmap")
library(ggmap)

## Loading required package: ggplot2

## Google's Terms of Service: https://cloud.google.com/maps-platform/terms/.

## Please cite ggmap if you use it! See citation("ggmap") for details.

Se elige cómo centro del mapa un punto más o menos central del barrio, para visualizar el mapa de manera mejor. Entonces se diseña el mapa de los accidentes.

bcn <- cartociudad_geocode("calle del bruc 80, 08009 barcelona")
mapa_bcn <- cartociudad_get_map(c(bcn$lat, bcn$lng), 2.7)
mapa <- ggmap::ggmap(mapa_bcn)
mapa + geom_point(aes(x = longitud, y = latitud), data = datos, size = 1)

## Warning: Removed 3 rows containing missing values (geom_point).

Se nota que hay un deslizamiento constante en los puntos. En los CSVs de origen de los años 2016 y 2017 se guardaba la información de latitud y longitud, entonces es suficiente mirar estos valores y compararlos con los valores obtenidos durante la transformación de UTM a latitud y longitud. Se encuentra así el valor que se tiene que restar.

datos$longitud <- datos$longitud - 0.001103145
datos$latitud <- datos$latitud - 0.00184649

Se vuelve a diseñar el mapa para verificar que se haya solucionado el problema de deslizamiento.

mapa + geom_point(aes(x = longitud, y = latitud), data = datos, size = 1)

Se puede notar que los accidentes se distribuyen de manera más o menos uniforme en todas las calles de este barrio. En particular, parecen ligeramente más frecuentes los accidentes al cruce entre Carrer de Pau Claris y Calle Provenza, al cruce entre Avenida Diagonal y Paseo de San Juan y en Plaza de Tetuán.